DBPWD='xxx' # 指定root密码
SLAVE='10.10.10.11'
REPLUSR='repl'
REPLPWD='xxx'

/**********************************************************************************************************/
# step1: 主库对象清单获取

mysql -uroot -p${DBPWD} > object_list.txt <<EOF
use information_schema;
-- 表清单
select '1. 表清单' note;
select table_schema,engine,row_format,count(*) cnt from information_schema.tables group by table_schema,engine,row_format order by 1,2,3;
-- 索引清单
select '2. 索引清单' note;
select database_name,count(*) cnt from mysql.innodb_index_stats group by database_name order by 1;
-- 分区清单
select '3. 分区清单' note;
select table_schema,count(*) cnt from information_schema.partitions where partition_name is not null group by table_schema order by 1;
-- 存储过程 & 函数清单
select '4. 存储过程 & 函数清单' note;
select routine_schema,count(*) cnt from information_schema.routines group by routine_schema order by 1;
-- 事件清单(job)
select '5. 事件清单(job)' note;
select event_schema,count(*) cnt from information_schema.events group by event_schema order by 1;
-- 触发器清单
select '6. 触发器清单' note;
select trigger_schema,count(*) cnt from triggers group by trigger_schema order by 1;
-- 视图清单
select '7. 视图清单' note;
select table_schema,count(*) cnt from information_schema.views group by table_schema order by 1;
EOF

/**********************************************************************************************************/
# step2: 主库目录信息获取(可前置)

mysql -uroot -p${DBPWD} <<EOF
select * from information_schema.global_variables where variable_name in('innodb_data_home_dir','innodb_log_group_home_dir','innodb_tmpdir','datadir','tmpdir');
EOF

mysql -uroot -p${DBPWD} <<EOF
grant replication slave
on *.*  
to '${REPLUSR}'@'${SLAVE}' identified by '${REPLPWD}';
select user,host from mysql.user where user='${REPLUSR}';
EOF

在备库验证连通信息，否则检查防火墙或者selinux设置(ubuntu下是apparmor)。

/**********************************************************************************************************/
# step3: 完整关闭主库

mysql -uroot -p${DBPWD} <<EOF
set global innodb_fast_shutdown=1;
EOF

mysqladmin -uroot -p${DBPWD} shutdown
service mysql stop

/**********************************************************************************************************/
# step4: 冷备主库

将step2中目录下的文件打包并copy到远程备库相应目录。

/**********************************************************************************************************/
# step5: 迁移mysql目录(部分可前置)

将step2中目录下的文件copy到对应目录文件中。

mkdir /data/mysql
cp -r /var/lib/mysql/* /data/mysql/.
chown -R mysql:mysql /data/mysql

修改前备份:
cp /etc/mysql/my.cnf /etc/mysql/my.cnf.$(date "+%F")

修改/etc/mysql/my.cnf配置：
symbolic-links = 1
datadir = /data/mysql
skip-networking
max_sp_recursion_depth = 255
default_storage_engine = innodb 
expire_logs_days = 2
max_length_for_sort_data = 10240
innodb_io_capacity = 8000
innodb_read_io_threads = 4
innodb_write_io_threads = 4
innodb_stats_on_metadata = off
max_connect_errors = 50
max_allowed_packet = 16M
innodb_print_all_deadlocks
table_open_cache = 5000
innodb_buffer_pool_size = 512MB
innodb_file_per_table = 1
innodb_open_files = 1000
innodb_buffer_pool_instances = 1
open_files_limit = 2000
skip-external-locking
innodb_log_buffer_size = 32M
key_buffer_size = 300MB
myisam_sort_buffer_size = 8M
myisam_use_mmap = on
myisam_recover_options = 'force,backup'
delay_key_write = on
log_bin_trust_function_creators = 1
query_cache_type = 0
query_cache_size = 0
max_connections = 500
log-bin = mysql-bin
binlog_format = mixed
server-id = 1
skip_slave_start = true
slave_net_timeout = 60
sync_master_info = 1 
sync_relay_log = 1
sync_relay_log_info = 1
sync_binlog = 1

/**********************************************************************************************************/
# step6: 修改apparmor策略(ubuntu下的selinux)

修改前备份:
cp /etc/apparmor.d/usr.sbin.mysqld /etc/apparmor.d/usr.sbin.mysqld.$(date "+%F")

修改/etc/apparmor.d/usr.sbin.mysqld：

  /var/lib/mysql/ r,
  /var/lib/mysql/** rwk,

将以上两行内容修改为：

  /data/mysql/ r,
  /data/mysql/** rwk,

apparmor为ubuntu下的类selinux安全系统，建议IT同事关闭。

/**********************************************************************************************************/
# step7: 开启主库

记录binlog信息

mysql -uroot -p${DBPWD} <<EOF
show master status;
EOF

检查log中是否有错误信息

vi /var/log/mysql/error.log

/**********************************************************************************************************/
# step8: 重启主库对外提供服务

将/etc/mysql/my.cnf中的skip-networking选项去除，然后重启主库数据库。

/**********************************************************************************************************/
# step9: 操作系统层优化(可前置)

echo 5 > /proc/sys/vm/swappiness
cat /proc/sys/vm/swappiness

cat >> /etc/rc.local <<EOF
echo 5 > /proc/sys/vm/swappiness
EOF

cat /etc/rc.local


history -c




/**********************************************************************************************************/
# 回滚

service mysql stop
cp /etc/mysql/my.cnf.$(date "+%F") /etc/mysql/my.cnf
cp /etc/apparmor.d/usr.sbin.mysqld.$(date "+%F") /etc/apparmor.d/usr.sbin.mysqld

service mysql start

查看/etc/log/mysql/error.log确认状态正常。
